package com.example.demo.mapper;

import com.example.demo.mapper.entity.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface PointsMallManageMapper {
    @Select("SELECT * FROM commodity")
    List<Commodity> getCommodityList();

    @Insert("INSERT INTO commodity(commodity_name,category,stock,image_url,description,points_required) " +
            "VALUES(#{commodityName},#{category},#{stock},#{imageUrl},#{description},#{pointsRequired})")
    int insertCommodity(Commodity newCommodity);

    @Select("SELECT * FROM commodity WHERE commodity_id = #{id}")
    Commodity selectCommodityById(int id);

    @Update("UPDATE commodity "+
            "SET image_url = #{imageUrl} "+
            "WHERE commodity_id = #{commodityId} ")
    void placeCommodity(Commodity currentCommodity);

    @Update("UPDATE `commodity` " +
            "SET commodity_name = #{commodityName},category = #{category} ,points_required = #{pointsRequired} ,stock = #{stock},description = #{description},updated_at = #{updatedAt} " +
            "WHERE commodity_id = #{commodityId}")
    void updateCommodityList(Commodity newCommodity);

    @Delete("DELETE FROM `commodity` WHERE commodity_id = #{commodityId}")
    void deleteCommodityById(Commodity commodity);

    @Delete("<script>" +
            "DELETE FROM commodity" +
            " WHERE commodity_id IN" +
            " <foreach collection='list' index='index' open='(' separator=',' close=')' item='commodityList' >" +
            "   #{commodityList.commodityId}" +
            " </foreach>" +
            "</script>")
    void deleteCommodityList(List<Commodity> commodityList);

    @Select("SELECT * FROM commodity WHERE commodity_id = #{commodityId}")
    Commodity selectImageUrl(int commodityId);

    @Select("SELECT * FROM `order` WHERE status=1 AND canceled=2")
    List<Order> getPendingOrderList();

    @Select("<script>" +
            "SELECT * " +
            " FROM `order` " +
            "<where> " +
            "  <if test='order.studentId != 0 and order.studentId != \"\" '>" +
            "     AND student_id = #{order.studentId} " +
            "  </if>" +
            "  <if test='order.commodityId != 0 and order.commodityId != \"\" '>" +
            "     AND commodity_id = #{order.commodityId} " +
            "  </if>" +
            "  <if test='order.status != 0 and order.status != \"\" '>" +
            "     AND status = #{order.status} " +
            "  </if>" +
            "  <if test='order.canceled != 0 and order.canceled != \"\" '>" +
            "     AND canceled = #{order.canceled} " +
            "  </if>" +
            "</where>" +
            "</script>")
    List<Order> searchByOrder(@Param("order") Order order);

    @Update("UPDATE `order` " +
            "SET student_id = #{studentId}, commodity_id = #{commodityId}, quantity = #{quantity}, status = #{status}, order_date = #{orderDate}, ship_date = #{shipDate}," +
            "cancel_date = #{cancelDate}, complete_date = #{completeDate}, address = #{address}, contact_name = #{contactName}, contact_phone = #{contactPhone}," +
            "points_used = #{pointsUsed}, tracking_number = #{trackingNumber}, notes = #{notes} " +
            "WHERE order_id = #{orderId}")
    void updateOrderList(Order newOrder);

    @Select("SELECT * FROM student_points")
    List<StudentPoints> getStudentPointsList();

    @Update("UPDATE student_points "+
            "SET points = #{points}, total_earned = #{totalEarned}, total_spent = #{totalSpent}, last_updated = #{lastUpdated} "+
            "WHERE student_id = #{studentId}")
    void updatePointsList(StudentPoints newStudent);

    @Select("SELECT * FROM exchange_record")
    List<ExchangeRecord> getExchangeRecordList();

    @Select("<script>" +
            "SELECT * " +
            " FROM `exchange_record` " +
            "<where> " +
            "  <if test='exchange.studentId != null and exchange.studentId != \"\" '>" +
            "     AND student_id = #{exchange.studentId} " +
            "  </if>" +
            "  <if test='exchange.commodityId != null and exchange.commodityId != \"\" '>" +
            "     AND commodity_id = #{exchange.commodityId} " +
            "  </if>" +
            "</where>" +
            "</script>")
    List<ExchangeRecord> searchExchange(@Param("exchange") ExchangeRecord exchange);

    @Select("SELECT points FROM student_points WHERE student_id=#{studentId}")
    int selectPointsById(int studentId);
    @Select("SELECT * FROM `order` WHERE status=2 AND canceled=2")
    List<Order> getShipedOrderList();

    @Select("SELECT * FROM `order` WHERE status=3 AND canceled=2")
    List<Order> getCompletedOrderList();

    @Select("SELECT * FROM `order` WHERE canceled=1")
    List<Order> getCanceledOrderList();
    @Select("SELECT * FROM `order` WHERE order_id=#{orderId}")
    Order selectOrderById(int orderId);
    @Select("SELECT * FROM `student_points` WHERE student_id = #{studentId}")
    StudentPoints selectStudentById(int studentId);

    @Select("SELECT * FROM `order_view` WHERE order_id = #{orderId}")
    List<OrderView> getOrderDataList(int orderId);
    @Select("SELECT cancel_date FROM `order` WHERE order_id = #{orderId}")
    String selectCancelDate(int orderId);
    @Select("SELECT order_date FROM `order` WHERE order_id = #{orderId}")
    String selectOrderDate(int orderId);
    @Select("SELECT complete_date FROM `order` WHERE order_id = #{orderId}")
    String selectCompleteDate(int orderId);
    @Select("SELECT ship_date FROM `order` WHERE order_id = #{orderId}")
    String selectShipDate(int orderId);

    @Update("UPDATE `order` " +
            "SET cancel_date = #{cancelDate}, canceled = 1, cancel_reason = #{cancelReason} " +
            "WHERE order_id = #{orderId}")
    void cancelOrder(Order newOrder);

    @Delete("DELETE FROM `order` WHERE order_id = #{orderId}")
    void deleteOrderById(int orderId);

    @Update("UPDATE `order` " +
            "SET ship_date = #{shipDate}, status = 2, distribute_method= #{distributeMethod} " +
            "WHERE order_id = #{orderId}")
    void shipOrder(Order newOrder);

    @Insert("INSERT INTO commodity(commodity_name, category, stock, image_url, description, points_required, version, is_offline) " +
            "VALUES(#{commodityName}, #{category}, #{stock}, #{imageUrl}, #{description}, #{pointsRequired}, #{version}, #{isOffline})")
    int addCommdity(Commodity curCommodity);

    @Update("UPDATE `order` " +
            "SET contact_name = #{contactName},contact_phone = #{contactPhone},province_id = #{provinceId}," +
            "city_id = #{cityId}, area_id = #{areaId}, address = #{address} " +
            "WHERE order_id = #{orderId}")
    int updateReceiverInfo(Order request);
    @Select("SELECT province FROM `hat_province` WHERE province_id = #{province}")
    String selectProvinceName(String province);
    @Select("SELECT city FROM `hat_city` WHERE city_id = #{city}")
    String selectCityName(String city);
    @Select("SELECT area FROM `hat_area` WHERE area_id = #{area}")
    String selectAreaName(String area);
}
